package com.kunbo.cn.controller.base;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.kunbo.cn.entity.EasyUiDataGrid;
import com.kunbo.cn.service.base.CfsServiceImpl;
import com.kunbo.cn.service.base.EasyuiServiceImpl;
import com.kunbo.cn.util.db.DbQuerySql;
/**
 * 自定义表单数据操作
 * @author zz
 *
 */
@Controller  
@RequestMapping("/crm/ActionFormUtil/*")  
public class ActionFormUtil
{
	
	@Autowired
    private JdbcTemplate jdbcTemplate;
	
    @Resource
    private EasyuiServiceImpl easyUiDto;
    
    @Resource
    private CfsServiceImpl cfsServiceImpl;
    
	
	private void debug(String s)
	{
		System.out.println(s);
	}
	/**
	 * 根据表名新增数据的接口
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="insert.do")  
    public @ResponseBody int insert(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String jsonStr = request.getParameter("jsonStr");
		
		JSONObject obj = new JSONObject(jsonStr);
		String tableName = obj.getString("title");
		JSONArray arrayFiled  = obj.getJSONArray("field");
		String uuid = "";
		if(arrayFiled.length()>0){
			
			JSONObject  o0 =arrayFiled.getJSONObject(0);
			String key = o0.getString("text");//拼接的字段	
			String val =o0.getString("value");//拼接的值
			 
			for(int i=1;i<arrayFiled.length();i++){
				JSONObject  o =arrayFiled.getJSONObject(i);
				String filed = ","+o.getString("text");
				String value = ","+o.getString("value");
				/*if("uuid".equals(o.getString("text"))) {
					uuid = o.getString("value");	//前台需要返回uuid		
				}*/
				key  +=filed ;
				val  +=value ;
			}
			String sql = "insert into "+tableName+" ("+key+") values ("+val+")";
			System.out.println("sql=="+sql);
		 	int i=0; 
		 	Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
            i = stmt.executeUpdate(sql); 
			/*String str = uuid+","+i ;
			
			System.out.println("sql=="+str);*/
            stmt.close();
			return 1;

		}else{
			return -1;
		}
	}
	/**
	 * 根据id,修改数据的接口
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="update.do")  
    public @ResponseBody int update(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String id = request.getParameter("id");
		String jsonStr = request.getParameter("jsonStr");
		
		JSONObject obj = new JSONObject(jsonStr);
		String tableName = obj.getString("title");
		JSONArray arrayFiled  = obj.getJSONArray("field");
		
		if(arrayFiled.length()>0){
			
			JSONObject  o0 =arrayFiled.getJSONObject(0);
			String filed0 = o0.getString("text");
			String value0 = o0.getString("value");
			String str =filed0+ "="+value0;//拼接的字段	
			for(int i=1;i<arrayFiled.length();i++){
				JSONObject  o =arrayFiled.getJSONObject(i);
				String filed = ","+o.getString("text");
				String value = o.getString("value");
				str  +=filed+"="+value;
			}
			System.out.println("循环外str:"+str);
			String sql = "update "+tableName+" set "+str+"  where id="+id;
			System.out.println("sql==="+sql);
		 
		 	int i=0; 
		 	Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
            i = stmt.executeUpdate(sql); 
			System.out.println("i=="+i);
			stmt.close();
			return i;
		}else{
			return -1;
		}
	}
	/** 
	 * 根据id,删除表中数据的接口
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="delete.do")  
    public @ResponseBody int delete(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String id = request.getParameter("id");
		String tableName = request.getParameter("tableName"); 
		int i=0; 
	    String sql = "delete from "+tableName+" where id="+Integer.parseInt(id);
	    Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql);  
        stmt.close();
	    return i;
	}
	/**
	 * 根据表名从table_manage删除表
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="deleteByTableName.do")  
    public @ResponseBody int deleteByTableName(HttpServletRequest request,HttpServletResponse response) throws Exception{  
    		String tableName = request.getParameter("tableName"); 
    		int i=0; 
    	    String sql = "delete from table_manage where title='"+tableName+"'";
    	    Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql);  	
        stmt.close();
        if(1==i) {
        	 dropTableByName(tableName); 
        }
	    return i; 		
	} 
	/**
	 * 根据表名从table_manage删除表
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public int  dropTableByName(String tableName) throws Exception {
		
	    int i=0; 
	    String sql = "drop table " + tableName;
	    System.out.println(sql);
	    PreparedStatement  preStmt =jdbcTemplate.getDataSource().getConnection().prepareStatement(sql);
        i=preStmt.executeUpdate();  
        System.out.println("创建副表成功："+i);
        preStmt.close();
	    return i;//返回影响的行数，1为执行成功  
	}
	
	
	/**
	 * 根据表名获取表中所有数据
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getByTableName.do")  
    public @ResponseBody EasyUiDataGrid getByTableName(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName"); 
		String create_user_id = request.getParameter("create_user_id");
		  
		String sql = "";
		sql += "SELECT * FROM  " + tableName+" order by id desc";
		//sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+" order by id desc";
		sql+=DbQuerySql.limit(page, rows);
		String sql_count = "select  *  from   "+ tableName ; 
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	
	@RequestMapping(value="getProcessRelFormDataByTableName.do")  
    public @ResponseBody EasyUiDataGrid getProcessRelFormDataByTableName(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName"); 
		String create_user_id = request.getParameter("create_user_id");
		  
		String sql = "";
		//sql += "SELECT m.* FROM  " + tableName+" m left join uflo_process_instance ins on m.uuid = ins.business_id_ left join uflo_his_process_instance hins on m.uuid=hins.business_id_ order by m.id desc";
		//sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+" order by id desc";
		sql += " SELECT";
		sql += " 	m.*, ins.current_task_,";
		sql += " 	hins.end_date_";
		sql += " FROM";
		sql += " 	" + tableName+" m";
		sql += " LEFT JOIN uflo_process_instance ins ON m.uuid = ins.business_id_";
		sql += " LEFT JOIN uflo_his_process_instance hins ON m.uuid = hins.business_id_";
		sql += " ORDER BY";
		sql += " 	m.id DESC";
		sql+=DbQuerySql.limit(page, rows);
		String sql_count = "select  *  from   "+ tableName ; 
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	/**
	 * <p>Title: getDataByCondition</p>
	 * <p>Description:根据条件获取表单设计器数据 </p>
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getDataByCondition.do")  
    public @ResponseBody EasyUiDataGrid getDataByCondition(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String jsonStr = request.getParameter("jsonStr");
		JSONObject obj = new JSONObject(jsonStr);
		String tableName = obj.getString("title"); 
		JSONArray arr = obj.getJSONArray("searchs");
		String sql = "";
		sql += "SELECT m.*,ins.current_task_,hins.end_date_ FROM  " + tableName+" m";
		sql += " LEFT JOIN uflo_process_instance ins ON m.uuid = ins.business_id_";
		sql += " LEFT JOIN uflo_his_process_instance hins ON m.uuid = hins.business_id_";
		String sql_count = "select  *  from   "+ tableName +" m"; 
		if(arr.length()>0) {
			for(int i=0;i<arr.length();i++) {
				JSONObject cobj = arr.getJSONObject(i);
				String searchField = cobj.getString("searchField");
				String val = cobj.getString("val");
				if(i==0) {
					sql += " where ";
					sql_count += " where ";
				}else {
					sql += " and ";
					sql_count += " and ";
				}
				if(cobj.get("searchCondition").equals("like")) {
					sql += " m."+searchField+" like '%"+val+"%'";
					sql_count += " m."+searchField+" like '%"+val+"%'";
				}else if(cobj.get("searchCondition").equals("not like")) {
					sql += " m."+searchField+" not like '%"+val+"%'";
					sql_count += " m."+searchField+" not like '%"+val+"%'";
				}else if(cobj.get("searchCondition").equals("=")) {
					sql += " m."+searchField+" = '"+val+"'";
					sql_count += " m."+searchField+" = '"+val+"'";
				}else if(cobj.get("searchCondition").equals("<>")) {
					sql += " m."+searchField+" <> '"+val+"'";
					sql_count += " m."+searchField+" <> '"+val+"'";
				}else if(cobj.get("searchCondition").equals(">")) {
					sql += " m."+searchField+" > '"+val+"'";
					sql_count += " m."+searchField+" > '"+val+"'";
				}else if(cobj.get("searchCondition").equals(">=")) {
					sql += " m."+searchField+" >= '"+val+"'";
					sql_count += " m."+searchField+" >= '"+val+"'";
				}else if(cobj.get("searchCondition").equals("<")) {
					sql += " m."+searchField+" < '"+val+"'";
					sql_count += " m."+searchField+" < '"+val+"'";
				}else if(cobj.get("searchCondition").equals("<=")) {
					sql += " m."+searchField+" <= '"+val+"'";
					sql_count += " m."+searchField+" <= '"+val+"'";
				}
				
			}
		}
		System.out.println(sql+"======ppppppppp");
		System.out.println(sql_count+"======nbnbbnbn");
		sql+=DbQuerySql.limit(page, rows);
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    	return l;
	}
	@RequestMapping(value="getByTableNameAndUserId.do")  
    public @ResponseBody EasyUiDataGrid getByTableNameAndUserId(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName");
		String create_user_id = request.getParameter("create_user_id");
		String sql = "";
		//根据用户的id 取出对应的数据
		sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+" order by id desc";
		sql+=DbQuerySql.limit(page, rows);
		String sql_count = "select  *  from   "+ tableName+" where create_user_id="+create_user_id ; 
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	@RequestMapping(value="getByTableNameAndUserIdAndPid.do")  
    public @ResponseBody EasyUiDataGrid getByTableNameAndUserIdAndPid(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName");
		String create_user_id = request.getParameter("create_user_id");
		String pid = request.getParameter("pid");
		String sql = "";
		//根据用户的id 取出对应的数据
		sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+" and pid='"+pid+"' order by id desc";
		System.out.println("sql："+sql);
		sql+=DbQuerySql.limit(page, rows);
		String sql_count = "select  *  from   "+ tableName+" where create_user_id="+create_user_id+" and pid='"+pid+"'" ; 
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	/**
	 * 根据表名和搜索条件获取表中所有数据
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getByTableNameAndSearch.do")  
    public @ResponseBody EasyUiDataGrid getByTableNameAndSearch(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName");
		String create_user_id = request.getParameter("create_user_id");
		String conditions = request.getParameter("conditions");
		  
		String sql = "";
		//sql += "SELECT * FROM  " + tableName+" where 1=1 "+conditions+" order by id desc";
		sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+conditions+" order by id desc";
		sql+=DbQuerySql.limit(page, rows);
		System.out.println("sql="+sql);
		String sql_count = "select  *  from   "+ tableName+" where create_user_id="+create_user_id+conditions ; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	@RequestMapping(value="getByTableNameAndSearchAndPid.do")  
    public @ResponseBody EasyUiDataGrid getByTableNameAndSearchAndPid(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName");
		String create_user_id = request.getParameter("create_user_id");
		String conditions = request.getParameter("conditions");
		String pid = request.getParameter("pid");
		  
		String sql = "";
		//sql += "SELECT * FROM  " + tableName+" where 1=1 "+conditions+" order by id desc";
		sql += "SELECT * FROM  " + tableName+" where create_user_id="+create_user_id+conditions+" and pid='"+pid+"' order by id desc";
		sql+=DbQuerySql.limit(page, rows);
		System.out.println("sql="+sql);
		String sql_count = "select  *  from   "+ tableName+" where create_user_id="+create_user_id+conditions+" and pid='"+pid+"'"; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	/**
	 * 根据表名获取表中所有数据
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getDataByUuid.do")  
    public @ResponseBody EasyUiDataGrid getDataByUuid(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String result ="";
		 
		String uuid = request.getParameter("uuid"); 
		String tableName = request.getParameter("tableName");
		  
		String sql = "select  *  from   " + tableName+"  where uuid='"+uuid+"'";
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql);
    		return l;
	}
	@RequestMapping(value="getDataById.do")  
    public @ResponseBody EasyUiDataGrid getDataById(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String result ="";
		 
		String id = request.getParameter("id"); 
		String tableName = request.getParameter("tableName");
		  
		String sql = "select  *  from   " + tableName+"  where id='"+id+"'";
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql);
    		return l;
	}
	/**
	 * 获取所有的表格
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getAllTable.do")  
    public @ResponseBody EasyUiDataGrid getAllTable(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		  
		String sql = "";
		sql += "SELECT * FROM table_manage";
		sql+=DbQuerySql.limit(page, rows);
		
		/*String sql_count = "select count(*) total from  table_manage " ;*/ 
		String sql_count = "select  *  from  table_manage " ; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	/**
	 * 根据type 获取所有的子表
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getChildTableByType.do")  
    public @ResponseBody EasyUiDataGrid getChildTableByType(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		  
		String sql = "";
		sql += "SELECT * FROM table_manage where type=1";
		sql+=DbQuerySql.limit(page, rows);
		
		/*String sql_count = "select count(*) total from  table_manage " ;*/ 
		String sql_count = "select  *  from  table_manage where type=1"  ; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	
	/**
	 * 根据表名和父表uuid获取字表数据
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getSonDataByUuid.do")  
    public @ResponseBody EasyUiDataGrid getSonDataByUuid(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName"); 
		String uuid = request.getParameter("uuid");
		  
		String sql = "";
		sql += "SELECT * FROM "+tableName+" where uuid='"+uuid+"'";
		sql+=DbQuerySql.limit(page, rows);
		
		String sql_count = "select  *  from  "+tableName+" where  uuid='"+uuid+"'";
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}

	@RequestMapping(value="getByType.do",produces="application/json; charset=UTF-8")  
    public @ResponseBody List<Map<String, Object>> getByType(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		String type = request.getParameter("type"); 
		List<Map<String, Object>> arr = cfsServiceImpl.getByType(type);
			return arr;
		 
	 }
	
}

